﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


/// <summary>
/// Summary description for TourYeuCau
/// </summary>
public class TourYeuCau
{
    SqlCommand cmd;
    SqlDataReader dr;
	public TourYeuCau()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public void createTourYeuCau(ObjTourYeuCau objTourYeuCau)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauCreate", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[11];
        sqla[0] = new SqlParameter("@Ho_ten", DbType.String);
        sqla[1] = new SqlParameter("@Tinh", DbType.String);
        sqla[2] = new SqlParameter("@Dia_chi", DbType.String);
        sqla[3] = new SqlParameter("@Dia_diem", DbType.String);
        sqla[4] = new SqlParameter("@Email", DbType.String);
        sqla[5] = new SqlParameter("@So_Dien_thoai", DbType.String);
        sqla[6] = new SqlParameter("@So_nguoi", DbType.String);
        sqla[7] = new SqlParameter("@Thoi_gian", DbType.String);
        sqla[8] = new SqlParameter("@Ten_co_quan", DbType.String);
        sqla[9] = new SqlParameter("@Yeu_cau", DbType.String);
        sqla[10] = new SqlParameter("@Loai_hinh_du_lich", DbType.String);
        
        sqla[0].Value = objTourYeuCau.Ho_ten;
        sqla[1].Value = objTourYeuCau.Tinh;
        sqla[2].Value = objTourYeuCau.Dia_chi;
        sqla[3].Value = objTourYeuCau.Dia_diem;
        sqla[4].Value = objTourYeuCau.Email;
        sqla[5].Value = objTourYeuCau.So_dien_thoai;
        sqla[6].Value = objTourYeuCau.So_nguoi;
        sqla[7].Value = objTourYeuCau.Thoi_gian;
        sqla[8].Value = objTourYeuCau.Ten_co_quan;
        sqla[9].Value = objTourYeuCau.Yeu_cau;
        sqla[10].Value = objTourYeuCau.Loai_hinh_du_lich;
      
        


        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }
    public List<ObjTourYeuCau> getOrderYeuCauByStatus(string sql)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauGetByStatus", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("sql", DbType.String);
        sqla[0].Value = sql;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTourYeuCau objTouryeucau;
        List<ObjTourYeuCau> lst = new List<ObjTourYeuCau>();
        while (dr.Read())
        {
            objTouryeucau = new ObjTourYeuCau();
            objTouryeucau.Order_yeu_cau_id = int.Parse(dr["order_yeu_cau_id"].ToString());
            objTouryeucau.Ho_ten = dr["ho_ten"].ToString();
            objTouryeucau.Tinh = dr["tinh"].ToString();
            objTouryeucau.Dia_chi = dr["dia_chi"].ToString();
            objTouryeucau.So_dien_thoai = dr["so_dien_thoai"].ToString();
            objTouryeucau.Ngay_tao = dr["ngay_tao"].ToString();
            objTouryeucau.Status = int.Parse(dr["status"].ToString());
            objTouryeucau.Email = dr["email"].ToString();
            objTouryeucau.Dia_diem = dr["dia_chi"].ToString();
            objTouryeucau.So_nguoi =int.Parse(dr["so_nguoi"].ToString());
            objTouryeucau.Ten_co_quan = dr["ten_co_quan"].ToString();
            objTouryeucau.Trang_thai = int.Parse(dr["trang_thai"].ToString());
            objTouryeucau.Loai_hinh_du_lich = dr["loai_hinh_du_lich"].ToString();
            lst.Add(objTouryeucau);
        }
        sqlConnection.Close();
        return lst;

    }
    public List<ObjTourYeuCau> getTourYeuCauById(int id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauGetById", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("order_yeu_cau_id", DbType.Int32);
        sqla[0].Value = id;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTourYeuCau objTouryeucau;
        List<ObjTourYeuCau> lst = new List<ObjTourYeuCau>();
        while (dr.Read())
        {
            objTouryeucau = new ObjTourYeuCau();
            //objTouryeucau.Order_yeu_cau_id = int.Parse(dr["Order_yeu_cau_id"].ToString());
            objTouryeucau.Ho_ten = dr["ho_ten"].ToString();
            objTouryeucau.Tinh = dr["tinh"].ToString();
            objTouryeucau.Dia_chi = dr["dia_chi"].ToString();
            objTouryeucau.So_dien_thoai = dr["so_dien_thoai"].ToString();
            //objTouryeucau.Ngay_tao = dr["ngay_tao"].ToString();
            //objTouryeucau.Status = int.Parse(dr["status"].ToString());
            objTouryeucau.Email = dr["email"].ToString();
            objTouryeucau.Dia_diem = dr["dia_chi"].ToString();
            objTouryeucau.So_nguoi = int.Parse(dr["so_nguoi"].ToString());
            objTouryeucau.Ten_co_quan = dr["ten_co_quan"].ToString();
            //objTouryeucau.Trang_thai = int.Parse(dr["trang_thai"].ToString());
            objTouryeucau.Loai_hinh_du_lich = dr["loai_hinh_du_lich"].ToString();
            lst.Add(objTouryeucau);
        }
        dr.Close();
        sqlConnection.Close();
        return lst;
    }
    public void TourYeuCauUpdateStatus(int status, int order_yeu_cau_id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauUpdateStatus", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[2];
        sqla[0] = new SqlParameter("status", DbType.Int16);
        sqla[1] = new SqlParameter("order_yeu_cau_id", DbType.Int32);
        sqla[0].Value = status;
        sqla[1].Value = order_yeu_cau_id;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }
    public void TourYeuCauDeleteByXml(string xml)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauDeleteByXml", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("doc", DbType.String);

        sqla[0].Value = xml;

        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();


    }
    public List<ObjTourYeuCau> TourYeuCauSearch(string sql)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TourYeuCauSearch", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("txt", DbType.String);
        sqla[0].Value = sql;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTourYeuCau objTouryeucau;
        List<ObjTourYeuCau> lst = new List<ObjTourYeuCau>();
        while (dr.Read())
        {
            objTouryeucau = new ObjTourYeuCau();
            objTouryeucau.Order_yeu_cau_id = int.Parse(dr["order_yeu_cau_id"].ToString());
            objTouryeucau.Ho_ten = dr["ho_ten"].ToString();
            objTouryeucau.Tinh = dr["tinh"].ToString();
            objTouryeucau.Dia_chi = dr["dia_chi"].ToString();
            objTouryeucau.So_dien_thoai = dr["so_dien_thoai"].ToString();
            objTouryeucau.Ngay_tao = dr["ngay_tao"].ToString();
            objTouryeucau.Status = int.Parse(dr["status"].ToString());
            objTouryeucau.Email = dr["email"].ToString();
            objTouryeucau.Dia_diem = dr["dia_chi"].ToString();
            objTouryeucau.So_nguoi = int.Parse(dr["so_nguoi"].ToString());
            objTouryeucau.Ten_co_quan = dr["ten_co_quan"].ToString();
            objTouryeucau.Trang_thai = int.Parse(dr["trang_thai"].ToString());
            objTouryeucau.Loai_hinh_du_lich = dr["loai_hinh_du_lich"].ToString();
            lst.Add(objTouryeucau);
        }
        sqlConnection.Close();
        return lst;

    }
}